﻿--- Eliminar las funciones existentes sin importar el numero ni tipo de parametros
SELECT * FROM "DropFunction_AWIL"('FuncSeguridadConsecutivo');
--- crear una nueva funcion
CREATE FUNCTION "FuncSeguridadConsecutivo"(
	 "@OP" CHARACTER VARYING(64)
	,"@codConsecutivo" character varying(32)
)RETURNS 
	character varying(32)
AS $$ 
DECLARE

	Auxiliar character varying(256);
BEGIN

	IF "@OP"='getNextConsecutivo' THEN
		IF NOT EXISTS (SELECT 1 FROM "SeguridadConsecutivo" WHERE "codConsecutivo"="@codConsecutivo") THEN
			Auxiliar = 'No existe un consectivo con el codigo "' || "@codConsecutivo" || '", verifique.';
			RAISE EXCEPTION '%', Auxiliar;
		END IF;

		IF NOT EXISTS (SELECT 1 FROM "SeguridadConsecutivo" WHERE "codConsecutivo"="@codConsecutivo" and activo=true) THEN
			Auxiliar = 'El consectivo con el codigo "' || "@codConsecutivo" || '" NO ESTA ACTIVO, verifique.';
			RAISE EXCEPTION '%', Auxiliar;
		END IF;

		--- obtengo el proximo consecutivo
		select 
			('00000' || cast(("proximoConsecutivo"+1) as character varying(6))) INTO Auxiliar
		FROM 
			"SeguridadConsecutivo" 
		WHERE 
			"codConsecutivo"="@codConsecutivo";

		Auxiliar = substring(Auxiliar,length(Auxiliar)-5,6);

		select 
			"prefijoConsecutivo" || '-' || Auxiliar INTO Auxiliar
		FROM 
			"SeguridadConsecutivo" 
		WHERE 
			"codConsecutivo"="@codConsecutivo";

		--- Incremento el consecutivo
		UPDATE "SeguridadConsecutivo" SET "proximoConsecutivo"="proximoConsecutivo"+1 WHERE "codConsecutivo"="@codConsecutivo";
		
	RETURN Auxiliar;
	END IF;

	IF "@OP"='getCurrentConsecutivo' THEN
		IF NOT EXISTS (SELECT 1 FROM "SeguridadConsecutivo" WHERE "codConsecutivo"="@codConsecutivo") THEN
			Auxiliar = 'No existe un consectivo con el codigo "' || "@codConsecutivo" || '", verifique.';
			RAISE EXCEPTION '%', Auxiliar;
		END IF;

		IF NOT EXISTS (SELECT 1 FROM "SeguridadConsecutivo" WHERE "codConsecutivo"="@codConsecutivo" and activo=true) THEN
			Auxiliar = 'El consectivo con el codigo "' || "@codConsecutivo" || '" NO ESTA ACTIVO, verifique.';
			RAISE EXCEPTION '%', Auxiliar;
		END IF;

		--- obtengo el proximo consecutivo
		select 
			('00000' || cast("proximoConsecutivo" as character varying(6))) INTO Auxiliar
		FROM 
			"SeguridadConsecutivo" 
		WHERE 
			"codConsecutivo"="@codConsecutivo";

		Auxiliar = substring(Auxiliar,length(Auxiliar)-5,6);

		select 
			"prefijoConsecutivo" || '-' || Auxiliar INTO Auxiliar
		FROM 
			"SeguridadConsecutivo" 
		WHERE 
			"codConsecutivo"="@codConsecutivo";

	RETURN Auxiliar;
	END IF;
	
	--- Controlo que se haya digitado el OP correctamente
	IF "@OP" IS NOT NULL THEN
		Auxiliar = 'El OP "' || "@OP" || '" No existe, verifique.';
		RAISE EXCEPTION '%', Auxiliar;
	RETURN '';
	END IF;
END;
$$ LANGUAGE 'plpgsql';